package com.shopping.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.shopping.modean.Category;
import com.shopping.modean.Product;
import com.shopping.modean.SaleItem;
import com.shopping.util.DBHelper;

public class SaleItemDAO {

	/**
	 * 新增SaleItem
	 */
	public void insert(SaleItem i) {
		
		Connection conn = DBHelper.getConn();
		String sql = "insert into(id,product_id,unit_price,pcount,order_id) sale_item values(null,?,?,?,?)";
		System.out.println(sql);
		PreparedStatement pstmt = DBHelper.getPStmt(conn, sql);
		try {
			pstmt.setInt(1, i.getProductId());
			pstmt.setDouble(2, i.getUnitPrice());
			pstmt.setInt(3, i.getPcount());
			pstmt.setInt(4, i.getOrderId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeStmt(pstmt);
			DBHelper.closeConn(conn);
		}
		
	}
	
	/**
	 * 新增SaleItem
	 */
	public void insertBath(int orderId,List<SaleItem> items) {
		
		Connection conn = DBHelper.getConn();
		String sql = "insert into sale_item(id,product_id,unit_price,pcount,order_id) values(null,?,?,?,?)";
		System.out.println(sql);
		PreparedStatement pstmt = DBHelper.getPStmt(conn, sql);
		try {
			for(SaleItem item : items) {
				pstmt.setInt(1, item.getProductId());
				pstmt.setDouble(2, item.getUnitPrice());
				pstmt.setInt(3, item.getPcount());
				pstmt.setInt(4, orderId);
				//pstmt.addBatch();
				pstmt.executeUpdate();
			}
			//pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeStmt(pstmt);
			DBHelper.closeConn(conn);
		}
		
	}

	/**
	 * 获取所有的Product
	 */
	public List<Product> getProducts() {
		List<Product> products = new ArrayList<Product>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConn();
			String sql = "select * from product";
			rs = DBHelper.executeQuery(conn, sql);
			while(rs.next()) {
				Product p = new Product();
				p.setId(rs.getInt("id"));
				p.setDescr(rs.getString("descr"));
				p.setNormalPrice(rs.getDouble("normal_price"));
				p.setMemberPrice(rs.getDouble("member_price"));
				p.setPdate(new Date(rs.getTimestamp("pdate").getTime()));
				p.setCategoryId(rs.getInt("category_id"));
				products.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeRs(rs);
			DBHelper.closeConn(conn);
		}
		return products;
	}
	
	/**
	 * 获取所有的Product
	 */
	public List<Product> getProducts(int pageNo,int pageSize) {
		List<Product> products = new ArrayList<Product>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConn();
			String sql = "select * from product limit " + (pageNo-1)*pageSize + "," + pageSize;
			System.out.println(sql);
			rs = DBHelper.executeQuery(conn, sql);
			while(rs.next()) {
				Product p = new Product();
				p.setId(rs.getInt("id"));
				p.setDescr(rs.getString("descr"));
				p.setNormalPrice(rs.getDouble("normal_price"));
				p.setMemberPrice(rs.getDouble("member_price"));
				p.setPdate(new Date(rs.getTimestamp("pdate").getTime()));
				p.setCategoryId(rs.getInt("category_id"));
				products.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeRs(rs);
			DBHelper.closeConn(conn);
		}
		return products;
	}
	
	/**
	 * 获取最新Product
	 */
	public List<Product> getLasterProducts(int count) {
		List<Product> products = new ArrayList<Product>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConn();
			String sql = "select * from product limit " + count;
			System.out.println(sql);
			rs = DBHelper.executeQuery(conn, sql);
			while(rs.next()) {
				Product p = new Product();
				p.setId(rs.getInt("id"));
				p.setDescr(rs.getString("descr"));
				p.setNormalPrice(rs.getDouble("normal_price"));
				p.setMemberPrice(rs.getDouble("member_price"));
				p.setPdate(new Date(rs.getTimestamp("pdate").getTime()));
				p.setCategoryId(rs.getInt("category_id"));
				products.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeRs(rs);
			DBHelper.closeConn(conn);
		}
		return products;
	}
	
	
	
	/**
	 * 根据pid获取所有的Category 
	 */
	public List<Category> getCategorys(int pid) {
		List<Category> categorys = new ArrayList<Category>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConn();
			String sql = "select * from category where pid = " + pid;
			rs = DBHelper.executeQuery(conn, sql);
			while(rs.next()) {
				Category c = new Category();
				c.setId(rs.getInt("id"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("descr"));
				c.setPid(rs.getInt("pid"));
				c.setGrade(rs.getInt("grade"));
				c.setLeaf(rs.getInt("leaf") == 1 ? true : false);
				categorys.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeRs(rs);
			DBHelper.closeConn(conn);
		}
		return categorys;
	}
	
	/**
	 * 根据ID获取product
	 * 
	 * return ID不存在时返回null
	 * 
	 */
	public Product getProductById(int id) {
		Product p = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConn();
			String sql = "select * from product where id = " + id;
			rs = DBHelper.executeQuery(conn, sql);
			if(rs.next()) {
				p = new Product();
				p.setId(rs.getInt("id"));
				p.setDescr(rs.getString("descr"));
				p.setNormalPrice(rs.getDouble("normal_price"));
				p.setMemberPrice(rs.getDouble("member_price"));
				p.setPdate(new Date(rs.getTimestamp("pdate").getTime()));
				p.setCategoryId(rs.getInt("category_id"));
			} 
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeRs(rs);
			DBHelper.closeConn(conn);
		}
		return p;
	}
	
	/**
	 * 根据PID获取子节点
	 */
	public List<Category> getCategorysByPid(int pid) {
		List<Category> categorys = new ArrayList<Category>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConn();
			String sql = "select * from category where pid = " + pid;
			rs = DBHelper.executeQuery(conn, sql);
			while(rs.next()) {
				Category c = new Category();
				c.setId(rs.getInt("id"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("desrc"));
				c.setPid(rs.getInt("pid"));
				c.setGrade(rs.getInt("grade"));
				c.setLeaf(rs.getInt("leaf") == 1 ? true : false); 
				categorys.add(c);
				if(!c.isLeaf()) {
					getCategorysByPid(c.getId());
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeRs(rs);
			DBHelper.closeConn(conn);
		}
		return categorys;
	}

	/**
	 * 更新父节点的叶子属性
	 */
	public void updateCategoryLeafById(int pid, boolean isLeaf) {
		Connection conn = DBHelper.getConn();
		String sql = "update category set leaf = ? where id = ?";
		System.out.println(sql);
		PreparedStatement pstmt = DBHelper.getPStmt(conn, sql);
		try {
			pstmt.setInt(1, isLeaf ? 1:0);
			pstmt.setInt(2, pid);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeStmt(pstmt);
			DBHelper.closeConn(conn);
		}
	}
	
	/**
	 * 根据id更新
	 */
	public void updateById(Product p) {
		Connection conn = DBHelper.getConn();
		String sql = "update product set descr = ?,category_id = ?,normal_price = ?,member_price = ? where id = ?";
		System.out.println(sql);
		PreparedStatement pstmt = DBHelper.getPStmt(conn, sql);
		try {
			pstmt.setString(1, p.getDescr());
			pstmt.setInt(2, p.getCategoryId());
			pstmt.setDouble(3, p.getNormalPrice());
			pstmt.setDouble(4,p.getMemberPrice());
			pstmt.setInt(5, p.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeStmt(pstmt);
			DBHelper.closeConn(conn);
		}
	}
	
	/**
	 * 根据id删除分类
	 */
	public void deleteById(int id) {
		Connection conn = DBHelper.getConn();
		String sql = "delete from category where id = " + id + " or pid = " + id;
		System.out.println(sql);
		PreparedStatement pstmt = DBHelper.getPStmt(conn, sql);
		try {
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeStmt(pstmt);
			DBHelper.closeConn(conn);
		}
	}

	/**
	 * 获取总页数
	 */
	public int getTotalCount() {
		int total = 0;
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConn();
			String sql = "select count(*) as total from product";
			rs = DBHelper.executeQuery(conn, sql);
			if(rs.next()) {
				total = rs.getInt("total");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeRs(rs);
			DBHelper.closeConn(conn);
		}
		return total;
	}

	
	
		
}
